package lookas.dao;

import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import lookas.Laucher;
import lookas.XmlParser;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class MoviesDao {
	private static final Log log = LogFactory.getLog(MoviesDao.class);

	private SimpleJdbcTemplate simpleJdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	public Map<String, Object> film(String link) {
		String sql = "select f.tytul T, f.rok R, f.ocena O, f.opis P "
				+ "from film f where f.link = ?";
		return simpleJdbcTemplate.queryForMap(sql, link);
	}

	public List<Map<String, Object>> filmy() {
		String sql = "select f.tytul T, k.katalog K, f.ocena O, k.link L from katalog k "
				+ "inner join film f on k.link=f.link where k.valid=1 order by f.ocena desc";
		return simpleJdbcTemplate.queryForList(sql);
	}

	public boolean exists(String katalog) {
		String sql = "select count(*) c from katalog t where t.katalog = ?";

		return simpleJdbcTemplate.queryForInt(sql, katalog) > 0;
	}

	private void updateFilm(Map<String, String> data) {
		try {
			String sql;
			sql = "update film set tytul=?, rok=?, ocena=?, opis=? where link=?";
			simpleJdbcTemplate.update(sql, data.get(XmlParser.TYTUL), data
					.get(XmlParser.ROK), data.get(XmlParser.OCENA), data
					.get(XmlParser.OPIS), data.get(XmlParser.LINK));
		} catch (DataAccessException e) {
			log.warn(e.getMessage());
		}
	}

	private void updateKatalog(Map<String, String> data) {
		String sql;
		sql = "select count(0) from film where link=?";
		int f = simpleJdbcTemplate.queryForInt(sql, data.get(XmlParser.LINK));
		if(f>0)
			updateFilm(data);
		else
			insertData(data);
		try {
			sql = "update katalog set link=?, valid=1 where katalog=?";
			simpleJdbcTemplate.update(sql, data.get(XmlParser.LINK), data
					.get(Laucher.KATALOG));
		} catch (DataAccessException e) {
			log.warn(e.getMessage());
		}
	}

	private void insertData(Map<String, String> data) {
		String sql;
		sql = "insert into film(tytul, rok, link, ocena, opis) values (?,?,?,?,?)";
		try {
			simpleJdbcTemplate.update(sql, data.get(XmlParser.TYTUL), data
					.get(XmlParser.ROK), data.get(XmlParser.LINK), data
					.get(XmlParser.OCENA), data.get(XmlParser.OPIS));
			sql = "insert into katalog(link, katalog, valid) values (?,?,?)";
			simpleJdbcTemplate.update(sql, data.get(XmlParser.LINK), data
					.get(Laucher.KATALOG), 1);
		} catch (DataAccessException e) {
			log.warn(e.getMessage());
		}
	}

	public void insertORupdateData(Map<String, String> data) {
		String sql;
		if (data.get(Laucher.BLAD) != null) {
			sql = "insert into katalog(katalog,valid) values (?,0)";
			try {
				simpleJdbcTemplate.update(sql, data.get(Laucher.KATALOG));
			} catch (DataAccessException e) {
				log.warn(e.getMessage());
			}
			return;
		}

		final String kat = data.get(Laucher.KATALOG);
		final String link = data.get(XmlParser.LINK);

		sql = "select count(0) from katalog where katalog = ?";
		int q = simpleJdbcTemplate.queryForInt(sql, kat);
		if (q > 0)
			updateKatalog(data);
		else
			insertData(data);

		final String plakat = data.get(XmlParser.PLAKAT);

		simpleJdbcTemplate.getJdbcOperations().execute(
				new ConnectionCallback() {
					public Object doInConnection(Connection con)
							throws SQLException, DataAccessException {

						try {
							URL url = new URL(plakat);
							URLConnection connection = url.openConnection();
							int length = connection.getContentLength();
							InputStream stream = url.openStream();
							String sql = "update film set plakat = ? where link = ?";
							PreparedStatement ps = con.prepareStatement(sql);
							ps.setBinaryStream(1, stream, length);
							ps.setString(2, link);
							return ps.executeUpdate();
						} catch (Exception e) {
							log.warn(e.getMessage());
							return null;
						}
					}
				});
	}

	public void invalid(String kat) {
		simpleJdbcTemplate.update(
				"update katalog set valid = 0 where katalog = ?", kat);
	}

	public List<Map<String, Object>> infilmy() {
		String sql = "select k.valid V, f.tytul T, k.katalog K, k.link L from katalog k "
				+ "inner join film f on k.link=f.link  order by f.tytul";
		return simpleJdbcTemplate.queryForList(sql);
	}
}
